Magnum One/Magnum One (Mid-American Digital) (Disc Manufacturing).iso/d18/lotus1.arc/UNIT123.PAS

Pascal/Delphi Source File | 1991-04-28 | 23KB | 638 lines

{$N+,E+}  (*  $N+ compiles for 80x87 which is used                 *)
          (*  automatically if available.  E+ activates the        *)
          (*  80X87 emulator which will be used if a coprocessor   *)
          (*  is not present.  I understand that these are program *)
          (*  wide options and cannot be used in a unit separately *)

(*  Written by Dan Glanz, Alexandria, Virginia (76672,2572), May, 1989. *)
(*  as a public service.                                                *)
(*  There are no restrictions on use and no gaurantees that it works.   *)

(*    All I ask is a smidgeon of credit.                          *)
(*  If you include this in a program, leave the credit line in. *)
(*  If you modify the unit, add your own credit line.           *)

(*    This is a Turbo Pascal 5.0 unit designed to allow reading and writing
    of Lotus 1-2-3,    Symphony, VP-Planner and other such files using the
    Lotus 1-2-3    file format.

              ************** update information  ***************

    This version is updated to allow reading and writing of range names and
    extents and column widths.  The structure of the "Lotus_Record_Type" has
    been modified to variant records for efficiency in data storage.

              **************************************************

    Lotus 1-2-3 uses 8 byte reals (TP's double's).  Any program using
    Lotus 1-2-3 data must either use a math coprocessor {$N+} or
    coprocessor emulation {$N+,E+}

    For demonstration purposes, a separate program called TEST123
    is included in the ARC file.

    It reads any Lotus format file and copies out label, integer, real
    column width, range identification and the current value of formula cells
    to a file in the same directory with the same name but with an extension
    of '.WK!'  It does not copy formulas and other such information.
    It is primarily designed to allow access to the DATA.  However, it
    does read column widths, and range names and extents.

    Take note that Lotus rows and columns are numbered starting with 0.
    that is Column A is 0 and Row 1 is 0.

    Lotus_Version is set up as a typed constant as if the file were
    a Lotus version 1.0 or 1a type file.  Change it if you need to.
    The program automatically writes a version record at the beginning of
    the file when the file is opened for writing by calling
    Open_Lotus_Write_File.  It must do this or Lotus 1-2-3 will not
    allow use of the file.  If you have used Open_Lotus_Read_File to open
    a file to be copied or accessed, then the version of that file is
    substituted for the default Lotus_Version.

    If you want to use the unit to create a Lotus formatted file directly,
    you must provide the row and column of the data in Lotus.Row and
    Lotus.Column (starting with row 0 and column 0), define the format
    in Lotus.Format (default seems to be 255) set the value in either
    Lotus.Integer_Value, Lotus.Real_Value, Lotus.Column_Width, or
    Lotus.Range_Name, Range_Start Column, etc., or Lotus.Label_Value.
    Then set Lotus.Cell_Type := to Integer_Type, Real_Type, Column_Width_Type,
    RAnge_Type or Label_Type as the case may be    and call Write_Lotus_Record.

    Note: When you write your own labels in Label_Value, make sure you put
    a ' or " or ^ as the first character of the string.  Also, you may be
    able to include formulas in a worksheet you are creating by writing out
    a label cell containing with the formula and then deleting
    the ', ", or ^ in the spreadsheet itself, perhaps by using a macro.

    When you call Close_Lotus_Write_File, an end of file record is written
    and the file is automatically closed.

    Lotus 1-2-3 is a trademark of Lotus Corporation.

*)

Unit Unit123;
Interface

uses crt, dos;

Const
    Lotus_Version : integer = 1028;  {1028 for Lotus 1}
                                     {1029 for Symphony 1.0}
                                     {1030 for Lotus 2 & Symphony 1.1}

Type
    Lotus_Cell_Type = (Version_Type, End_Of_File_Type, Blank_Type,
                       Integer_Type, Real_Type, Label_Type,
                       Formula_Type, Unidentified_Type, Range_Type,
                       Column_Width_Type);

    Lotus_Record_Type =
        Record
            Cell_Type_Code    : Integer;
            Cell_Length        : Integer;
            Format            : Byte;
            Column            : integer;
            Row                : integer;
            Cell_Type       : Lotus_Cell_Type;
            Alpha_Column    : String[8];
            case integer of
                 8:   (Column_Width       : Byte);
                 11:  (Range_Name         : string[16];
                       Range_Start_Column : integer;
                       Range_Start_Row    : integer;
                       Range_End_Column   : integer;
                       Range_End_Row      : integer;
                       Alpha_Range_Start  : string;
                       Alpha_Range_End    : String);
                 13:  (Integer_Value      : integer);
                 14:  (Real_Value          : double);
                 15:  (Label_Value          : string;
                       Zero                  : byte);
                 16:  (Formula_Value      : double;
                       Formula_Length     : integer;
                       Formula              : array [0 .. 255] of byte);
                 255: (Unidentified       : array [0 .. 511] of byte);
        end;
var
    Lotus_Read_File_Name    : PathStr;
    Lotus_Read_File_DirStr  : DirStr;
    Lotus_Read_File_NameStr : NameStr;
    Lotus_Read_File_ExtStr  : ExtStr;

    Lotus_Read_File         : file;

    Lotus_Write_File_Name    : PathStr;
    Lotus_Write_File        : file;

    Lotus_End_Of_File        : boolean;
    Lotus_Version_Name        : string;

    Lotus                    : Lotus_Record_Type;

Procedure Get_Input_File_Name;
Procedure Open_Lotus_Read_File;
Procedure Get_Version_Name;
Procedure Get_Alpha_Column(var Alpha_Column_ID : String; Column_Number, Row_Number:integer);
Procedure Read_Type_and_Length;
Procedure Read_Format_Info;
Procedure Read_Lotus_Record;
Procedure Print_Lotus_Record;
Procedure Close_Lotus_Read_File;

Procedure Make_New_File_Name;
Procedure Open_Lotus_Write_File;
Procedure Write_Type_and_Length;
Procedure Write_Format_Info;
Procedure Write_Lotus_Record;
Procedure Close_Lotus_Write_File;

implementation

(****************************************************************)

Procedure Get_Input_File_Name;    {Solicits the input file name from }
                                  {the console.  If the extension is }
                                  {ommitted, it substitutes .WKS }

begin
    Write('Enter name of Lotus File ');    {Get the file path name}
    Readln(Lotus_Read_File_Name);
    FSplit(Lotus_Read_File_Name,Lotus_Read_File_DirStr,
        Lotus_Read_File_NameStr,Lotus_Read_File_ExtStr);
    If Lotus_Read_File_ExtStr = '' then Lotus_Read_File_ExtStr := '.WKS';
    Lotus_Read_File_Name := Lotus_Read_File_DirStr + Lotus_Read_File_NameStr +
        Lotus_Read_File_ExtStr;
end;



(****************************************************************)

Procedure Read_Type_and_Length;    {Could be changed to a single }
                                   {BlockRead since Cell_Type_Code}
                                   {Cell_Length are adjacent in the record}
                                   {definition and on the file.}
begin
    BlockRead(Lotus_Read_File, Lotus.Cell_Type_Code, 2);
    BlockRead(Lotus_Read_File, Lotus.Cell_Length,    2);

{   OPTIONAL}
{   BlockRead(Lotus_Read_File, Lotus.Cell_Type_Code, 4);   }

end;

(****************************************************************)

Procedure Write_Type_and_Length;        {Could be changed to a single }
                                        {BlockWrite since format, column}
                                        {and row are adjacent in the record}
                                        {definition and on the file.}
begin
    BlockWrite(Lotus_Write_File, Lotus.Cell_Type_Code, 2);
    BlockWrite(Lotus_Write_File, Lotus.Cell_Length,    2);

{   OPTIONAL}
{   BlockWrite(Lotus_Write_File, Lotus.Cell_Type_Code, 4);   }

end;

(****************************************************************)

Procedure Get_Alpha_Column(var Alpha_Column_ID : String; Column_Number, Row_Number:integer);
Var
   First_Char : char;
   Second_Char : char;
   Row_String  : string[6];
Begin
   First_Char := char(64 + Column_Number div 26);
   Second_Char := char(65 + Column_Number -((byte(First_Char)-64) * 26));
   If First_Char = #64 then First_Char := #32;
   Str(Row_Number + 1, Row_String);
   Alpha_Column_ID := First_Char + Second_Char + Row_String;
end;


(****************************************************************)

Procedure Read_Format_Info;             {Could be changed to a single }
                                        {BlockRead since format, column}
                                        {and row are adjacent in the record}
                                        {definition and on the file.}
Var
    Alpha_Column_ID : string;

begin
    BlockRead(Lotus_Read_File, Lotus.Format, 1);
    BlockRead(Lotus_Read_File, Lotus.Column, 2);
    BlockRead(Lotus_Read_File, Lotus.Row,    2);

{   OPTIONAL}
{   BlockRead(Lotus_Read_File, Lotus.Format, 5);   }

    Get_Alpha_Column(Alpha_Column_ID, Lotus.Column, Lotus.Row);
    Lotus.Alpha_Column := Alpha_Column_ID;

end;

(****************************************************************)

Procedure Write_Format_Info;            {Could be changed to a single }
                                        {BlockWrite since format, column}
                                        {and row are adjacent in the record}
                                        {definition and on the file.}
begin
    BlockWrite(Lotus_Write_File, Lotus.Format, 1);
    BlockWrite(Lotus_Write_File, Lotus.Column, 2);
    BlockWrite(Lotus_Write_File, Lotus.Row,    2);

{   OPTIONAL}
{   BlockWrite(Lotus_Write_File, Lotus.Format, 5);   }

end;

(****************************************************************)

Procedure Open_Lotus_Read_File;
begin
{$I-}
    Assign(Lotus_Read_File,Lotus_Read_File_Name);
    Reset(Lotus_Read_File,1);
    If IoResult <> 0 then
        begin
            Writeln('Error opening file ', Lotus_Read_File_Name);
            halt;
        end;
{$I+}
     Read_Lotus_Record;                      {Read the first record}
                                             {If the first record is}
                                             {not a Version_Type record}
                                             {then this is not a Lotus File}

    If Lotus.Cell_Type <> Version_Type then
       begin
            Writeln('This is not a Lotus File');
            Halt
       end;

    Lotus_End_Of_File := false;
end;

(****************************************************************)

Procedure Open_Lotus_Write_File;
begin
{$I-}
    Assign(Lotus_Write_File,Lotus_Write_File_Name);
    ReWrite(Lotus_Write_File,1);
    If IoResult <> 0 then
        begin
            Writeln('Error opening file ', Lotus_Write_File_Name);
            halt;
        end;
{$I+}

{ Automatically write a version type record at the beginning of the file }
{ Lotus_Version is a typed constant set to Version 1.0 or 1A by default  }
{ If you have used Open_Lotus_File to read another Lotus file, then it   }
{ will have already read the version type record from the input file     }

    Lotus.Cell_Type_Code := 0;
    Lotus.Cell_Length    := 2;
    Write_Type_and_Length;
    BlockWrite(Lotus_Write_File,Lotus_Version,2);

end;

(****************************************************************)

Procedure Close_Lotus_Read_File;
begin
    Close(Lotus_Read_File);
end;

(****************************************************************)

Procedure Close_Lotus_Write_File;
begin
{ Write an end of file record at the end of the file }
    Lotus.Cell_Type_Code := 1;
    Lotus.Cell_Length    := 0;
    Write_Type_and_Length;            {End the file with a type 1 record}
    Close(Lotus_Write_File);
end;

(****************************************************************)

Procedure Get_Version_Name;
begin
    Case Lotus_Version of
        1028:
            Lotus_Version_Name := 'Lotus 1-2-3 Version 1.0 or 1A';
        1029:
            Lotus_Version_Name := 'Symphony Version 1.0';
        1030:
            Lotus_Version_Name := 'Lotus 1-2-3 Version 2.0, 2.1 or Symphony Version 1.1';
        Else
            Lotus_Version_Name := 'Unidentified';
    end;
end;

(****************************************************************)

Procedure Read_Lotus_Record;

var
    Alpha_Column_ID : string;

begin
    FillChar(Lotus, SizeOf(Lotus), #0);
    Read_Type_and_Length;

    Case Lotus.Cell_Type_Code of

        0:  begin                             {Version Record}
                                              {There should be only one}
                                              {record of this type and it}
                                              {will normally be read when}
                                              {you call Open_Lotus_Read_File}

                Lotus.Cell_Type := Version_Type;
                BlockRead(Lotus_Read_File, Lotus_Version, 2);
                Get_Version_Name;
            end;

        1:  begin                                        {End of File}
                Lotus.Cell_Type := End_Of_File_Type;
                Lotus_End_of_File := True;
            end;

        8:                                               {Column width type}
            begin
                Lotus.Cell_Type := Column_Width_Type;
                BlockRead(Lotus_Read_File, Lotus.Column, 2);
                BlockRead(Lotus_Read_File, Lotus.Column_Width,1);
                Get_Alpha_Column(Alpha_Column_ID, Lotus.Column, 0);
                Lotus.Alpha_Column := Alpha_Column_ID;
            end;

        11: begin                                        {Range definition}
                Lotus.Cell_Type := Range_Type;
                BlockRead(Lotus_Read_File, Lotus.Range_Name[1],16);
                Lotus.Range_Name[0] := Char(16);
                BlockRead(Lotus_Read_File, Lotus.Range_Start_Column,2);
                BlockRead(Lotus_Read_File, Lotus.Range_Start_Row,2);
                BlockRead(Lotus_Read_File, Lotus.Range_End_Column,2);
                BlockRead(Lotus_Read_File, Lotus.Range_End_Row,2);
                Get_Alpha_Column(Alpha_Column_ID, Lotus.Range_Start_Column,
                        Lotus.Range_Start_Row);
                Lotus.Alpha_Range_Start := Alpha_Column_ID;
                Get_Alpha_Column(Alpha_Column_ID, Lotus.Range_End_Column,
                        Lotus.Range_End_Row);
                Lotus.Alpha_Range_End := Alpha_Column_ID;
             end;

        12: begin                                        {Blank Record}
                Lotus.Cell_Type := Blank_Type;
                Read_Format_Info;
            end;

        13: begin                                         {Integer}
                Lotus.Cell_Type := Integer_Type;
                Read_Format_Info;
                BlockRead(Lotus_Read_File, Lotus.Integer_Value, 2);
            end;

        14: begin                                         {Real Value}
                Lotus.Cell_Type := Real_Type;
                Read_Format_Info;
                BlockRead(Lotus_Read_File, Lotus.Real_Value, 8);
            end;

        15: begin                                         {Label}
                Lotus.Cell_Type := Label_Type;
                Read_Format_Info;
                If Lotus.Cell_Length > 261 then
                    begin
                       Writeln('Big problem! Label at Row', Lotus.Row, ' Column ', Lotus.Column, ' has length > 255');
                       Halt;
                    end;
                BlockRead(Lotus_Read_File, Lotus.Formula, Lotus.Formula_Length);
                BlockRead(Lotus_Read_File, Lotus.Label_Value[1], Lotus.Cell_Length - 6);
                Lotus.Label_Value[0] := char(Lotus.Cell_Length - 6);
                BlockRead(Lotus_Read_File, Lotus.Zero, 1);
            end;

        16: begin                                         {Formula}
                Lotus.Cell_Type := Formula_Type;
                Read_Format_Info;
                BlockRead(Lotus_Read_File, Lotus.Formula_Value, 8);
                BlockRead(Lotus_Read_File, Lotus.Formula_Length, 2);
                If Lotus.Formula_Length > 255 then
                    begin
                       Writeln('Big problem! Formula cell at Row', Lotus.Row, ' Column ', Lotus.Column, ' has length > 255');
                       Halt;
                    end;
                BlockRead(Lotus_Read_File, Lotus.Formula, Lotus.Formula_Length);
            end;

        Else                                              {Unidentified}
            begin
                Lotus.Cell_Type := Unidentified_Type;

            {    Use the following line only if you are sure that the length }
            {    of the unidentified data type is less than 512 characters.  }
            {    I the unidentified data cell is more than 512 byte long,    }
            {    it could cream the program by overwriting code.             }
            {    The check on cell length protects against this. But,        }
            {    if you don't know the maximum cell length, the safest       }
            {    approach is the approach I have taken, just skip the        }
            {    unknown data cell                                           }
            {                                                                }
            {    If Lotus.Cell_Length > 512 then                             }
            {        begin                                                   }
            {           Writeln('Big problem! Cell at row ', Lotus.Row, ' Column ', Lotus.Coulmn, ' has  length > 512');   }
            {           Halt;                                                }
            {        end;                                                    }
            {                                                                }
            {    BlockRead (Lotus_Read_File, Lotus.Unidentified , Lotus.Cell_Length);}


            {    This is the safest way.}

                Seek(Lotus_Read_File, FilePos(Lotus_Read_File) + Lotus.Cell_Length);

            end;
        end;
end;

(****************************************************************)

Procedure Print_Lotus_Record;
begin
    If Lotus.Cell_Type = Blank_Type then exit;  {If you really want to}
                                                {show all of the blank}
                                                {records, delete this line}
    Writeln;
    Case Lotus.Cell_Type of

        Version_Type:
            Writeln(Lotus_Version_Name, ' Id Code = ',Lotus_Version);

        End_Of_File_Type:
            Writeln('End of File');

        Column_Width_Type:
            Writeln('Column ', Lotus.Alpha_Column , ' Width = ' , Lotus.Column_Width);

        Range_Type:
            begin
                Writeln('Lotus Range Cell');
                Write(' Range Name = ', Lotus.Range_Name);
                Write(' Range Start = ', Lotus.Alpha_Range_Start);
                Writeln(' Range End = ', Lotus.Alpha_Range_End);
            end;

        Blank_Type:
            begin
                Write('Lotus Cell ', Lotus.Alpha_Column);
                Write(' Format = ', Lotus.Format);
                Writeln(' Blank Cell');
            end;

        Integer_Type:
            begin
                Write('Lotus Cell ', Lotus.Alpha_Column);
                Write(' Format = ', Lotus.Format);
                Writeln(' Integer = ', Lotus.Integer_Value);
            end;

        Real_Type:
            begin
                Write('Lotus Cell ', Lotus.Alpha_Column);
                Write(' Format = ', Lotus.Format);
                Writeln(' Real = ', Lotus.Real_Value);
            end;

        Label_Type:
            begin
                Write('Lotus Cell ', Lotus.Alpha_Column);
                Write(' Format = ', Lotus.Format);
                Writeln(' Label = ', Lotus.Label_Value);
            end;

        Formula_Type:
            begin
                Write('Lotus Cell ', Lotus.Alpha_Column);
                Write(' Format = ', Lotus.Format);
                Writeln(' Formula Value = ', Lotus.Formula_Value);
            end;

        End_Of_File_Type:
            Writeln('End of file detected.');

        Unidentified_Type:
            begin
                Writeln('Unidentified Cell. OpCode = ', Lotus.Cell_Type_Code,
                ' Length = ', Lotus.Cell_Length);
            end;

        Else
            begin
                Writeln(' Unidentified Cell. OpCode = ', Lotus.Cell_Type_Code);
            end;
        end;

end;

(****************************************************************)

Procedure Write_Lotus_Record;

begin
    Case Lotus.Cell_Type of

        Column_Width_Type:
            begin
                Lotus.Cell_Type_Code := 8;
                Lotus.Cell_Length := 3;
                Write_Type_and_Length;
                BlockWrite(Lotus_Write_File , Lotus.Column, 2);
                BlockWrite(Lotus_Write_File , Lotus.Column_Width, 1);
            end;

        Range_Type:
            begin
                Lotus.Cell_Type_Code := 11;
                Lotus.Cell_Length := 24;
                Write_Type_and_Length;
                BlockWrite(Lotus_Write_File,Lotus.Range_Name[1], 16);
                BlockWrite(Lotus_Write_File,Lotus.Range_Start_Column,2);
                BlockWrite(Lotus_Write_File,Lotus.Range_Start_Row,2);
                BlockWrite(Lotus_Write_File,Lotus.Range_End_Column,2);
                BlockWrite(Lotus_Write_File,Lotus.Range_End_Row,2);
            end;

        Blank_Type:
            Exit;

        Integer_Type:
            begin
                Lotus.Cell_Type_Code := 13;
                Lotus.Cell_Length := 7;
                Write_Type_and_Length;
                Write_Format_Info;
                BlockWrite(Lotus_Write_File,Lotus.Integer_Value,2);
            end;

        Real_Type:
            begin
                Lotus.Cell_Type_Code := 14;
                Lotus.Cell_Length := 13;
                Write_Type_and_Length;
                Write_Format_Info;
                BlockWrite(Lotus_Write_File,Lotus.Real_Value,8);
            end;

        Label_Type:
            begin
                Lotus.Cell_Type_Code := 15;
                Lotus.Cell_Length := 6 + Length(Lotus.Label_Value);
                Lotus.Zero := 0;
                Write_Type_and_Length;
                Write_Format_Info;
                BlockWrite(Lotus_Write_File,Lotus.Label_Value[1],Length(Lotus.Label_Value));
                BlockWrite(Lotus_Write_File,Lotus.Zero, 1);
            end;

        Formula_Type:    {NOTE: ONLY COPIES OUT THE CURRENT VALUE AS A REAL}
                         { If you want to copy the formula then also       }
                         { BlockWrite Lotus.Formula_Value.                 }
                         { See Read_Lotus_File for how to interpret length }
                         { Also, you must change the Cell_Type_Code to 16  }
            begin
                Lotus.Cell_Type_Code := 14;
                Lotus.Cell_Length := 13;
                Write_Type_and_Length;
                Write_Format_Info;
                BlockWrite(Lotus_Write_File,Lotus.Formula_Value,8);
            end;

        Else
            begin
            end;
        end;
end;

(****************************************************************)

Procedure Make_New_File_Name;

begin
    Lotus_Write_File_Name := Lotus_Read_File_DirStr + Lotus_Read_File_NameStr +
         '.WK!';
end;

begin
end.